<table border=1>
    <tr>
        <th ROWSPAN=2>Fecha</th>
        <th COLSPAN=3>Comprobante de Pago</th>
        <th COLSPAN=3>Proveedor</th>
        <th ROWSPAN=2>Base</th>
        <th ROWSPAN=2>IGV</th>
        <th ROWSPAN=2>Total</th>
        <th ROWSPAN=2>Tipo Cambio</th>
    </tr>
    <tr>
        <th>Tipo Documento</th>
        <th>Serie</th>
        <th>Numero</th>
        <th>Tipo</th>
        <th>RUC</th>
        <th>Razon Social</th>
    </tr>

    <?php
    require_once('PHPExcel.php');
    require_once 'PHPExcel/IOFactory.php';
    require '../connection.php';



    $files = array('../uploads/01 - Ventas.xlsx', '../uploads/02 - Ventas.xlsx', '../uploads/03 - Ventas.xlsx', '../uploads/04 - Ventas.xlsx');

    $period = 0;
    foreach ($files as $i => $value) {
        $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize' => '20MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
        $objectReader = PHPExcel_IOFactory::createReader('Excel2007');
        $objectReader->setReadDataOnly(true);
        $objPHPExcel = $objectReader->load($files[$i]);
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $starting = 11;
        $end = 110;
        $post = 11;
        $period++;

        $db = getConnection();
        foreach ($objWorksheet->getRowIterator() as $row) {
            if ($row->getRowIndex() > '11') {
                $val = $objWorksheet->getCell("C" . $post)->getValue();
                if ($val != "") {
                    echo '<tr>' . "\n";
                    //------------------------------------------------------------------
                    //fecha  
                    $value = floor($val);
                    $date = new DateTime('1899-12-30');
                    $date->add(new DateInterval('P' . $value . 'D'));
                    $fecha = $date->format('Y-m-d');
                    echo '<td>' . $fecha . '</td>';
                    //------------------------------------------------------------------
                    $tipo_documento = $objWorksheet->getCell("E" . $post)->getValue();
                    echo '<td>' . $tipo_documento . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $serie_documento = $objWorksheet->getCell("F" . $post)->getValue();
                    echo '<td>' . $serie_documento . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $numero_documento = $objWorksheet->getCell("G" . $post)->getValue();
                    echo '<td>' . $numero_documento . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $proveedor_tipo = $objWorksheet->getCell("H" . $post)->getValue();
                    echo '<td>' . $proveedor_tipo . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $proveedor_ruc = $objWorksheet->getCell("I" . $post)->getValue();
                    echo '<td>' . $proveedor_ruc . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $proveedor_razon_social = utf8_decode($objWorksheet->getCell("J" . $post)->getValue());
                    echo '<td>' . $proveedor_razon_social . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $base_imponible = $objWorksheet->getCell("L" . $post)->getValue();
                    echo '<td>' . $base_imponible . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $igv = $objWorksheet->getCell("P" . $post)->getValue();
                    echo '<td>' . $igv . '</td>' . "\n";
                    //------------------------------------------------------------------
                    $total = $objWorksheet->getCell("R" . $post)->getValue();
                    echo '<td>' . $total . '</td>' . "\n";
                    //------------------------------------------------------------------
                    //$tipo_cambio = $objWorksheet->getCell("Y" . $post)->getValue();
                    $tipo_cambio = "0";
                    echo '<td>' . $tipo_cambio . '</td>' . "\n";
                    //------------------------------------------------------------------
                    echo '</tr>' . "\n";
                    //------------------------------------------------------------------
                    //------------------------------------------------------------------
                    $id_tipo_cambio = 0;
                    $proveedor_id = 0;
                    /*if ($proveedor_razon_social == "ANULADO") {
                        $proveedor_id = 2;
                    } else {*/
                        $sql_list = "SELECT id FROM operators WHERE code = :ruc";
                        try {
                            $db = getConnection();
                            $stmt = $db->prepare($sql_list);
                            $stmt->bindParam("ruc", $proveedor_ruc);
                            $stmt->execute();
                            $rst = $stmt->fetchObject();
                            if ($rst) {
                                $proveedor_id = $rst->id;
                            }
                        } catch (PDOException $e) {
                            echo '{"error11":{"text":' . $e->getMessage() . '}}';
                        }
                        if ($proveedor_id == 0 && $proveedor_razon_social != "ANULADO") {
                            $sql_add = "INSERT INTO operators(name, code, type) VALUES (:name, :ruc, :type)";
                            $type = 2;
                            try {
                                $stmt = $db->prepare($sql_add);
                                $stmt->bindParam("name", $proveedor_razon_social);
                                $stmt->bindParam("ruc", $proveedor_ruc);
                                $stmt->bindParam("type", $type);
                                $stmt->execute();
                                $proveedor_id = $db->lastInsertId();
                            } catch (PDOException $e) {
                                echo '{"error12":{"text":' . $e->getMessage() . '}}';
                            }
                        }
                    //}
                    //------------------------------------------------------------------
                    $sql_add = "INSERT INTO import_document(date,document_id,serie,number,subtotal,igv,total,operator_id,type_change,operator_type,type,period,year) 
              VALUES (:date,:document_id,:serie,:number,:subtotal,:igv,:total,:operator_id,:type_change,:operator_type,:type,:period,:year)";

                    $type = 2; //1 - compras,   2 - ventas

                    $year = 2013;
                    try {
                        $stmt = $db->prepare($sql_add);
                        $stmt->bindParam("date", $fecha);
                        $stmt->bindParam("document_id", $tipo_documento);
                        $stmt->bindParam("serie", $serie_documento);
                        $stmt->bindParam("number", $numero_documento);
                        $stmt->bindParam("operator_id", $proveedor_id);
                        $stmt->bindParam("subtotal", $base_imponible);
                        $stmt->bindParam("igv", $igv);
                        $stmt->bindParam("total", $total);
                        $stmt->bindParam("type_change", $id_tipo_cambio);
                        $stmt->bindParam("operator_type", $proveedor_tipo);
                        $stmt->bindParam("type", $type);
                        $stmt->bindParam("period", $period);
                        $stmt->bindParam("year", $year);

                        $stmt->execute();
                    } catch (PDOException $e) {
                        echo '{"error3":{"text":' . $e->getMessage() . '}}';
                    }
                }

                $post++;
                //------------------------------------------------------------------            
            }
        }
    }





    $db = null;


    echo '</table>' . "\n";
    ?>
